package com.kehutong.house.service;

import com.kehutong.common.DoveClient;
import com.kehutong.common.ExcelUtil;
import com.kehutong.common.entity.Root;
import com.kehutong.common.enums.CardType;
import com.kehutong.common.enums.HouseType;
import com.kehutong.common.permission.DataPermissionQuery;
import com.kehutong.common.permission.PermissionNameDto;
import com.kehutong.common.permission.UserLoginInfo;
import com.kehutong.common.urlconfig.FileUrlConfig;
import com.kehutong.common.util.BigDecimalUtils;
import com.kehutong.common.util.ConvertUtils;
import com.kehutong.common.util.Token;
import com.kehutong.house.dao.HouseDao;
import com.kehutong.house.dao.ParkingDao;
import com.kehutong.house.dao.ShopDao;
import com.kehutong.house.entity.Building;
import com.kehutong.house.entity.Community;
import com.kehutong.house.entity.House;
import com.kehutong.house.enums.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.coraframework.bean.Beans;
import org.coraframework.inject.Inject;
import org.coraframework.json.JSONArray;
import org.coraframework.json.JSONObject;
import org.coraframework.logger.Logger;
import org.coraframework.logger.LoggerFactory;
import org.coraframework.mvc.http.Bean;
import org.coraframework.mvc.http.ReqMapping;
import org.coraframework.mvc.http.result.JSONResult;
import org.coraframework.mvc.http.result.Result;
import org.coraframework.mvc.webservice.WebService;
import org.coraframework.orm.Pool;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.Page;
import org.coraframework.orm.jdbc.execute.Condition;
import org.coraframework.orm.jdbc.execute.ICondition;
import org.coraframework.orm.jdbc.execute.PageCondition;
import org.coraframework.orm.jdbc.execute.QueryCondition;
import org.coraframework.orm.jdbc.execute.entity.FindArray;
import org.coraframework.orm.jdbc.execute.entity.FindOne;
import org.coraframework.orm.jdbc.execute.entity.FindPage;
import org.coraframework.util.EnumUtil;
import org.coraframework.util.Objects;

import java.io.File;
import java.io.InputStream;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@WebService("/basic/house/service")
public class HouseService {

	static final Logger logger = LoggerFactory.getLogger(HouseService.class);

	public final static String[] EXCEL_IMPORT_HEADER = {"项目/小区", "楼栋", "单元", "楼层", "房屋", "别名", "物业类型", "户型", "建筑面积(㎡)", "实用面积(㎡)","计费面积(㎡)"
			, "销售转态", "收房状态", "装修状态", "入住状态", "出租状态", "姓名", "手机号码", "证件号码"};

	private final static String businessType = "1102";

	@Inject
	private JdbcSession jdbcSession;
	@Inject
	private FileUrlConfig fileUrlConfig;
	@Inject
	private HouseDao houseDao;
	@Inject
	private ParkingDao parkingDao;
	@Inject
	private ShopDao shopDao;
	@Inject
	private DoveClient doveClient;
	@Inject
	private BasicService basicService;


	@ReqMapping("/list")
	public Object list(JSONObject jsonObject) {
		return jdbcSession.findArray(House.class)
				.eq("deleted", false)
				.eq(jsonObject,  "buildNo","ownerMobile","ownerNo")
				.in(jsonObject, "id")
				.in(jsonObject, "communityNo")
				.like(jsonObject, "buildName", "unitName", "floorName","name", "ownerName")
				.order("communityNo","asc")
				.order("buildNo","asc")
				.order("unitName","asc")
				.order("floorName", "asc")
				.order("name", "asc")
				.exe();
	}

	@ReqMapping("/page")
	public Object pageByParam(JSONObject jsonObject) {
		FindPage<House> find = jdbcSession.findPage(House.class)
				.eq(jsonObject,  "buildNo")
				.eq("deleted",false)
				.eq(jsonObject, "communityNo")
				.like(jsonObject, "buildName", "unitName", "floorName","name")
				.order("communityNo","asc")
				.order("buildNo","asc")
				.order("unitName","asc")
				.order("floorName", "asc")
				.order("name", "asc");
		find.page(jsonObject);
		return find.exe();
	}

	public List<House> list(String communityNo, String buildNo){
		return jdbcSession.findArray(House.class)
				.eq("communityNo", communityNo)
				.eq("buildNo", buildNo)
				.eq("deleted", false)
				.order("buildName", "ASC")
				.order("unitName", "ASC")
				.order("floorName", "ASC")
				.order("name", "ASC")
				.exe();
	}

	@ReqMapping("/get")
	public Object get(House house) {
		return house;
	}

	@ReqMapping("/getByIds")
	public Object getByIds(JSONObject jsonObject) {
		return jdbcSession.findArray(House.class)
				.in(jsonObject, "id")
				.exe();
	}

	@Bean(newInstance = true, copy = true)
	@ReqMapping("/save")
	public Object save(House house) {
		JSONResult result = checkHouse(house, false);
		if(Objects.nonNull(result)){
			return result;
		}

		jdbcSession.insert(house);

		return house;
	}

	@Bean(copy = true)
	@ReqMapping("/update")
	public Object update(House house, JSONObject json) throws Exception {
		JSONResult result = checkHouse(house, true);
		if(Objects.nonNull(result)){
			return result;
		}

		saveAttributeRecord(house, json);
		updateHouseConfig(house);

		jdbcSession.updateById(house);
		return Result.success();
	}

	private void saveAttributeRecord(House entity, JSONObject jsonObject) throws Exception {
		if(Objects.isEmpty(entity.getOwnerNo())){
			return;
		}

		JSONObject json = new JSONObject();
		json.put("operateName", jsonObject.getString("operateName"));
		json.put("operateNo", jsonObject.getString("operateNo"));
		json.put("businessId", entity.getId());
		json.put("businessType", businessType);
		json.put("currentNo", entity.getOwnerNo());
		json.put("currentName", entity.getOwnerName());
		json.put("startTime", LocalDateTime.now().format(DateTimeFormatter.ofPattern ("yyyy-MM-dd HH:mm:ss")));
		if(Objects.nonEmpty(entity.getOwnerNo())){
			House old = Pool.get(House.class, entity.getId());

			if(Objects.isEmpty(entity.getOwnerNo())){
				entity.setOwnerNo("");
			}

			if(Objects.isEmpty(old.getOwnerNo())){
				old.setOwnerNo("");
			}
			if(!Objects.equal(old.getOwnerNo(),entity.getOwnerNo())){
				json.put("beforeNo", old.getOwnerNo());
				json.put("beforeName", old.getOwnerName());
				JSONObject record = basicService.getAttributeRecord(json);
				if(Objects.nonEmpty(record)){
					record.put("endTime", LocalDateTime.now().format(DateTimeFormatter.ofPattern ("yyyy-MM-dd HH:mm:ss")));
					basicService.updateAttributeRecord(record);
				}
				basicService.saveAttributeRecord(json);
			}
		}
	}

	private void updateHouseConfig(House house) throws Exception {
		JSONObject updateHouseResult = doveClient.post("/charge/house/config/service/updateHouseConfig", (http) -> {
			JSONObject body = new JSONObject();
			body.put("houseNo", house.getId());
			body.put("ownerNo", house.getOwnerNo());
			body.put("ownerName", house.getOwnerName());

			http.setBody(body.toJSONString());
		});
	}

	@ReqMapping("/delete")
	public Object delete(House house) throws Exception {
		house.setDeleted(true);
		jdbcSession.updateById(house);

		//同步删除t_charge_house_config表数据
		JSONObject saveConfigResult = doveClient.post("/charge/house/config/service/deleteHouseConfig", (http) -> {
			JSONObject body = new JSONObject();
			body.put("houseNo",house.getId());
			http.setBody(body.toJSONString());
		});
		return Result.success();
	}

	@ReqMapping("/houseList")
	public Object houseList(JSONObject json) {
		logger.debug("{}=========", this.hashCode());
		Page<House> page = jdbcSession.findPage(House.class)
				.markBegin()
				.in(json, "id") .or()
				.in(json, "buildNo")
				.markEnd()
				.like(json, "unitName", "communityName", "buildName")
				.page(json).exe();
		return page;
	}

    @ReqMapping("/batchUpdateHouseOwner")
	public Object batchUpdateHouseOwner(JSONObject jsonObject){
	    List<House> list = jdbcSession.findArray(House.class)
                .eq(jsonObject,"ownerNo")
                .eq("deleted", false)
                .exe();

        list.forEach(item->{
            item.setOwnerNo(jsonObject.getString("ownerNo"));
            item.setOwnerName(jsonObject.getString("ownerName"));
            item.setOwnerMobile(jsonObject.getString("ownerMobile"));
        });

        jdbcSession.updateById(list);

        return Result.success();
    }

	@ReqMapping("/getHouseInfo")
	public Object getHouseInfo(JSONObject jsonObject){

		JSONArray houseList = jsonObject.getJSONArray("houseList");
		List<JSONObject> list = JSONObject.parseArray(houseList.toJSONString(), JSONObject.class);

		List<JSONObject> result = new ArrayList<>();
		list.forEach(json ->{
			FindOne<House> find = jdbcSession.findOne(House.class)
					.eq(json, "name", "communityName", "buildName", "floorName")
					.eq("deleted", false);
			String unitName = json.getString("unitName");
			if(Objects.nonEmpty(unitName)){
				find.eq("unitName", unitName);
			}
			//为空就不要这个条件
//			else{
//				find.isEmpty("unitName");
//			}

			House house = find.exe();

			if(Objects.nonNull(house)){
				StringBuilder builder = new StringBuilder();
				builder.append(house.getCommunityName()).append("-")
						.append(house.getBuildName()).append("-");
				if(Objects.nonEmpty(house.getUnitName())){
					builder.append(house.getUnitName()).append("-");
				}
				builder.append(house.getFloorName()).append("-")
						.append(house.getName());
				json.put("houseInfo", builder.toString());
				json.put("id",house.getId());
				result.add(json);
			}
		});
		return result;
	}

	public Object export(JSONObject jsonObject) throws Exception {

		File tmpFile = null;

		Boolean isModel = jsonObject.getBoolean("isModel");

		if(isModel){
			Workbook wb = generateWorkBook();

			tmpFile = ExcelUtil.writeXlsFile(wb, "房屋导入模板", ".xlsx");
		} else {
			jsonObject.put("pageNo",1);
			jsonObject.put("pageSize",Short.MAX_VALUE);
			Page<Map<String, Object>> page = this.page(jsonObject);
			List<Map<String, Object>> houseListMap = page.getList();

			Workbook wb = generateWorkBook(houseListMap);

			tmpFile = ExcelUtil.writeXlsFile(wb, "房屋信息", ".xlsx");
		}
		return Result.download(tmpFile);
	}

	public Object getHouseTransfer(Token token, JSONObject jsonObject, boolean isId) throws Exception {
		JSONArray data = new JSONArray(2);

		Integer level = jsonObject.getInteger("level");
		List<JSONObject> array = new ArrayList<>();
		if (level == 1) {
			FindArray<Community> find = jdbcSession.findArray(Community.class)
					.eq("deleted", false);

			PermissionNameDto dto = getCommunityPermissionNameDto();
			DataPermissionQuery.setAllDataSql(find, dto, null);
			List<Community> communities = find.exe(false);

			communities.forEach(community -> {
				array.add(communitie2JSONObject(community));
			});
			JSONObject root = root2JSONObject(token);
			data.add(root);
			data.add(array);
		} else if (level == 2) {
			String communityNo = jsonObject.getString("communityNo");
			FindArray<Building> find = jdbcSession.findArray(Building.class)
					.eq(jsonObject, "communityNo")
					.eq("deleted", false);

			PermissionNameDto dto = getBuildPermissionNameDto();

			DataPermissionQuery.setAllDataSql(find, dto, null);

			List<Building> buildings = find.exe(false);

			Community community = Pool.get(Community.class, communityNo);
			JSONObject root = communitie2JSONObject(community);
			buildings.forEach(build -> {
				array.add(build2JSONObject(communityNo, build));
			});
			data.add(root);
			data.add(array);
		} else if (level == 3) {
			String buildNo = jsonObject.getString("buildNo");

			List<String> notInHouseIds = new ArrayList<>();
			if(isId){
				notInHouseIds = getResidentHouseIds(jsonObject);
			}

			QueryCondition query = Condition.createQuery(false);
//					.authorize("h.departmentNo","h.departmentNos");
			query.eq("h.deleted", false);

			if(Objects.nonEmpty(buildNo)){
				query.eq("h.buildNo", buildNo);
			}

			if (Objects.nonEmpty(notInHouseIds)) {
				query.noIn("h.id", notInHouseIds);
			}
			PermissionNameDto dto = getHousePermissionNameDto();

			DataPermissionQuery.setAllDataSql(query, dto, null);


			List<Map<String, Object>> houses = houseDao.loadlist(query);

			Building build = Pool.get(Building.class, buildNo);
			JSONObject root = build2JSONObject(build.getCommunityNo(), build);
			houses.forEach(house -> {
				array.add(house2JSONObject(build, house));
			});

			data.add(root);
			data.add(array);
		}

		return Result.success(data);
	}

    public Object insert(Token token, House house, JSONObject jsonObject) throws  Exception {
		JSONResult result = checkHouse(house, false);
		if(Objects.nonNull(result)){
			return result;
		}

		this.setProperty(house, jsonObject);

		house.setCreate_by(token.getUsername());
		house.setCreateUserId(token.getUuid());
		house.setCreateDepartment(token.getDepartments());
		house.setCreateChildDepartment(getChildDepartments(token.getDepartments()));

		jdbcSession.insert(house);
		saveChargeHouseConfig(token, house);

		basicService.saveOperateRecord(token, house.getId(), businessType, "1", "");

		return house;
	}

	//同步新增t_charge_house_config表数据
	public Object saveChargeHouseConfig(Token token, House house){
		try {
			JSONObject saveConfigResult = doveClient.post("/charge/house/config/service/saveHouseConfig", (http) -> {
				JSONObject body = new JSONObject();
				body.put("communityNo",house.getCommunityNo());
				body.put("communityName",house.getCommunityName());
				body.put("id",house.getId());
				body.put("name",house.getName());
				body.put("buildNo",house.getBuildNo());
				body.put("buildName",house.getBuildName());
				body.put("unitName",house.getUnitName());
				body.put("mergeName",house.getMergeName());
				body.put("ownerName",house.getOwnerName());
				body.put("payArea",BigDecimalUtils.format(house.getPayArea(),4));
				body.put("ownerNo",house.getOwnerNo());
				body.put("deptNo",token.getDepartments().get(0));
				http.setBody(body.toJSONString());
			});
		} catch (Exception e) {
			e.printStackTrace();
			logger.info("saveChargeHouseConfig exception:{}",e.getMessage());
			return Result.error(-1, "新增资产发生异常");
		}
		return Result.success();
	}

	/**
	 * 报错或更新用户的业务标签
	 * @param house 房屋信息
	 * @throws Exception
	 */
	public List<String> saveOrUpdateTag(House house) throws Exception{
		JSONArray tagDatas = new JSONArray();

		PropertyType buildType = house.getPropertyType();
		if(Objects.nonNull(buildType)){
			JSONObject tagObj = new JSONObject();
			tagObj.put("tagGroupName", "建筑类别");
			tagObj.put("tagName", buildType.getDesc());
			tagDatas.add(tagObj);
		}

		if (Objects.nonEmpty(tagDatas)) {
			JSONObject result=doveClient.post("/admin/business/tag/service/batch/add", (http) -> {
				JSONObject body = new JSONObject();
				body.put("dataList", tagDatas);
				http.setBody(body.toJSONString());
			});

			if (result.getIntValue("code") == 200) {
				JSONArray data = result.getJSONArray("data");
				if(Objects.nonEmpty(data)){
					List<String> tagIds = new ArrayList<>();
					List<JSONObject> tags = JSONObject.parseArray(data.toJSONString(), JSONObject.class);
					tags.forEach(tag -> {
						tagIds.add(tag.getString("id"));
					});
					return tagIds;
				}
			}
		}
		return null;
	}

	public Object update(Token token, House house, JSONObject jsonObject) throws Exception {
		JSONResult result = checkHouse(house, true);
		if(Objects.nonNull(result)){
			return result;
		}

		this.setProperty(house, jsonObject);

		jdbcSession.updateById(house);

		basicService.saveOperateRecord(token, house.getId(), businessType, "2", "");

		this.updateChargeSetting(house);

		return Result.success(house);
	}

    private List<Map<String,Object>> getHouses(JSONObject jsonObject) throws Exception {
		QueryCondition query = Condition.createQuery(false);
//				.authorize("h.departmentNo","h.departmentNos");

		query.eq("h.deleted", false);

		PermissionNameDto dto = getHousePermissionNameDto();

		UserLoginInfo userLoginInfo = JSONObject.parseObject(jsonObject.toJSONString(), UserLoginInfo.class);
		this.setDataSql(query, dto, jsonObject, userLoginInfo);

		return houseDao.loadlist(query);
	}

	private List<Map<String, Object>> getParkings(JSONObject jsonObject) throws Exception {
		QueryCondition query = Condition.createQuery(false);
//				.authorize("p.departmentNo","p.departmentNos");

		query.eq("p.deleted", false);

		PermissionNameDto dto = getParkingsPermissionNameDto();

		UserLoginInfo userLoginInfo = JSONObject.parseObject(jsonObject.toJSONString(), UserLoginInfo.class);
		this.setDataSql(query, dto, jsonObject, userLoginInfo);
		return parkingDao.loadList(query);
	}

	private List<Map<String, Object>> getShops(JSONObject jsonObject) throws Exception {
		QueryCondition query = Condition.createQuery(false);
//				.authorize("s.departmentNo","s.departmentNos");

		query.eq("s.deleted", false);

		PermissionNameDto dto = getShopsPermissionNameDto();

		UserLoginInfo userLoginInfo = JSONObject.parseObject(jsonObject.toJSONString(), UserLoginInfo.class);
		this.setDataSql(query, dto, jsonObject, userLoginInfo);

		return shopDao.loadList(query);
	}

	public boolean setDataSql(ICondition find, PermissionNameDto dto, JSONObject jsonObject, UserLoginInfo userLoginInfo){
		Integer dataPvType = jsonObject.getIntValue("dataPvType");
		if(dataPvType == 2){
			DataPermissionQuery.setChildDepartmentDataSql(find ,dto, userLoginInfo);
			return false;
		}else if(dataPvType == 3){
			DataPermissionQuery.setManageDataSql(find ,dto, userLoginInfo);
			return true;
		}else{
			DataPermissionQuery.setAllDataSql(find , dto, userLoginInfo);
			return true;
		}
	}

	public Object permissionQuery(JSONObject jsonObject) {
		QueryCondition query = Condition.createQuery(false);
		query.eq("h.deleted", false);

		String mergeName = jsonObject.getString("mergeName");
		if(Objects.nonEmpty(mergeName)){
			query.like("h.mergeName", mergeName);
		}

		String buildNo = jsonObject.getString("buildNo");
		if(Objects.nonEmpty(buildNo)){
			query.eq("h.buildNo", buildNo);
		}
		PermissionNameDto dto = getHousePermissionNameDto();
		DataPermissionQuery.setAllDataSql(query, dto, null);
		List<Map<String, Object>> houses = houseDao.loadlist(query);
		return houses;
	}

	@ReqMapping("/permissionAssetsList")
	public Object permissionAssetsList(JSONObject jsonObject){
		try{
			List<Map<String, Object>> houses = getHouses(jsonObject);
			List<Map<String, Object>> parkings = getParkings(jsonObject);
			List<Map<String, Object>> shops = getShops(jsonObject);

			JSONObject result = new JSONObject();
			result.put("houses", houses);
			result.put("parkings", parkings);
			result.put("shops", shops);
			return result;
		}catch (Exception e){
			return Result.error(0, e.getMessage());
		}
	}

	public Object readFile(Token token, JSONObject jsonObject) throws Exception {
		InputStream is = fileUrlConfig.download(jsonObject.getString("fileId"));
		Workbook wb = WorkbookFactory.create(is);
		Sheet sheet = wb.getSheetAt(0);

		List<JSONObject> houseJsons = new ArrayList<>();
		// 第4行开始遍历获取数据
		for (int rIndex = sheet.getFirstRowNum() + 3; rIndex <= sheet.getLastRowNum(); rIndex++) {
			Row row = sheet.getRow(rIndex);

			if(Objects.isNull(row)){
				continue;
			}

			List<String> cellValues = new ArrayList<>();
			for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
				Cell cell = row.getCell(i);
				if (i < EXCEL_IMPORT_HEADER.length && Objects.nonNull(cell)) {
					cell.setCellType(CellType.STRING);
					String value = cell.getStringCellValue();
					if(Objects.nonEmpty(value)){
						cellValues.add(value);
					}
				}
			}
			if(Objects.isEmpty(cellValues)){
				continue;
			}

			JSONObject json = new JSONObject();
			// 校验数据
			checkExcelData(row, json);

			houseJsons.add(json);
		}

		JSONArray ownerJsons = new JSONArray();

		List<String> childDepartments = getChildDepartments(token.getDepartments());

		for (JSONObject json : houseJsons) {
			String buildName = json.getString("buildName");
			String unitName = json.getString("unitName");

			//房屋是否已经存在
			FindOne<House> find = jdbcSession.findOne(House.class)
					.eq("deleted", false)
					.eq("communityName", json.getString("communityName"))
					.eq("buildName", buildName)
					.eq("floorName", json.getString("floorName"))
					.eq("name", json.getString("name"));
			if (Objects.nonEmpty(unitName)) {
				find.eq("unitName", unitName);
			}
			House house = find.exe();

			//新增修改业主信息
			if (Objects.nonNull(house)) {
				setExcelData(house, json);
				jdbcSession.updateById(house);

				basicService.saveOperateRecord(token, house.getId(), businessType, "2", "上传文档修改");

				this.updateChargeSetting(house);
			} else {
				house = Pool.newInstance(House.class);
				setExcelData(house, json);

				house.setCreate_by(token.getUsername());
				house.setCreateUserId(token.getUuid());
				house.setCreateDepartment(token.getDepartments());
				house.setCreateChildDepartment(childDepartments);

				jdbcSession.insert(house);

				basicService.saveOperateRecord(token, house.getId(), businessType, "1", "上传文档新增");
			}

			if (Objects.nonEmpty(json.getString("mobile"))) {
				JSONObject ownerJson = new JSONObject();
				ownerJson.put("houseNo", house.getId());
				ownerJson.put("realname", json.getString("realname"));
				ownerJson.put("mobile", json.getString("mobile"));
                ownerJson.put("cardType", CardType.ID_CARD.getId());
				ownerJson.put("cardNo", json.getString("cardNo"));
				ownerJson.put("houseType", HouseType.HOUSE.getId());
				ownerJsons.add(ownerJson);
			}
		}

		//批量写入业主信息
		if (Objects.nonEmpty(ownerJsons)) {
			//批量新增员工跟进的住户信息
			doveClient.post("/crm/resident/service/batchSaveOrUpdateOwner", (http) -> {
				JSONObject body = new JSONObject();
				body.put("ownerList", ownerJsons);
				http.setBody(body.toJSONString());
			});
		}

		return Result.success();
	}

	public Object checkFile(JSONObject jsonObject) throws Exception {
		InputStream is = fileUrlConfig.download(jsonObject.getString("fileId"));
		Workbook wb = WorkbookFactory.create(is);
		Sheet sheet = wb.getSheetAt(0);

		// 3行表头 开始读
		Row firstRow = sheet.getRow(sheet.getFirstRowNum() + 2);
		List<String> errMsgs = new ArrayList<>();

		for (int i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++) {
			firstRow.getCell(i).setCellType(CellType.STRING);
			String s = firstRow.getCell(i).getStringCellValue();
			if (i < EXCEL_IMPORT_HEADER.length) {
				if (!EXCEL_IMPORT_HEADER[i].equals(s)) {
					errMsgs.add("请确认【" + EXCEL_IMPORT_HEADER[i] + "】为第" + (i + 1) + "列");
				}
			}
		}

		JSONObject errorJson = new JSONObject();
		// 表头校验结果
		if (!errMsgs.isEmpty()) {
			errorJson.put("normalCount", 0);
			errorJson.put("errCount", 0);
			errorJson.put("successCount", 0);
			errorJson.put("errMsgs", errMsgs);
			return Result.error(0, "数据解析异常", errorJson);
		}

		Map<Integer,Integer> errCount = new HashMap<>();
		Integer size = 0;

		// 第4行开始遍历获取数据
		for (int rIndex = sheet.getFirstRowNum() + 3; rIndex <= sheet.getLastRowNum(); rIndex++) {
			Row row = sheet.getRow(rIndex);

			if(Objects.isNull(row)){
				continue;
			}

			List<String> cellValues = new ArrayList<>();
			for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
				Cell cell = row.getCell(i);
				if (i < EXCEL_IMPORT_HEADER.length && Objects.nonNull(cell)) {
					cell.setCellType(CellType.STRING);
					String value = cell.getStringCellValue();
					if(Objects.nonEmpty(value)){
						cellValues.add(value);
					}
				}
			}
			if(Objects.isEmpty(cellValues)){
				continue;
			}

			// 校验数据
			checkExcelData(row, (rIndex + 1), errMsgs, errCount);

			size++;
		}

		errorJson.put("normalCount", size);
		if(size > 3000){
			errMsgs = new ArrayList<>();
			errMsgs.add("最多支持导入3000条数据");
			errorJson.put("errCount", 0);
			errorJson.put("successCount", 0);
			errorJson.put("errMsgs", errMsgs);
			return Result.error(0, "数据解析异常", errorJson);
		}

		// 数据读取结果
		if (!errMsgs.isEmpty()) {
			int errSum = 0;
			for (int val: errCount.keySet()) {
				errSum++;
			}

			errorJson.put("errMsgs", errMsgs);
			errorJson.put("errCount", errSum);
			errorJson.put("successCount", size - errSum);

			return Result.error(0, "数据解析异常", errorJson);
		}

		errorJson.put("errMsgs", errMsgs);
		errorJson.put("errCount", 0);
		errorJson.put("successCount", size);
		return Result.success("成功", errorJson);
	}

	public Object getHouses(House house) throws Exception{
		Building build = Pool.get(Building.class, house.getBuildNo());

		if (Objects.nonEmpty(build.getEmployeeNos())) {
			Map<String, JSONObject> employeeMap = getEmployeeMap(build.getEmployeeNos());
			house.setEmployeeNames(getEmployeeNames(build.getEmployeeNos(), employeeMap));
		}

		Community community = Pool.get(Community.class, house.getCommunityNo());
		if(Objects.nonNull(community)){
			house.setParent_ids(community.getParent_ids());
		}

		return house;
	}

	public Page<Map<String, Object>> page(JSONObject jsonObject) throws Exception {
		PageCondition query = Condition.createPage(false);
//				.authorize("h.departmentNo","h.departmentNos");

		JSONArray employeeNos = jsonObject.getJSONArray("employeeNos");
		if(Objects.nonEmpty(employeeNos)){
			query.markBegin();
			for (Object item: employeeNos) {
				query.like("b.employeeNos",String.valueOf(item)).or();
			}
			query.markEnd().and();
		}

		String search = jsonObject.getString("search");
		if(Objects.nonEmpty(search)){
			query.markBegin();
			query.like("h.mergeName", search).or();
			query.like("h.viewName", search).or();
			query.like("h.name", search).or();
			query.like("h.floorName", search).and();
			query.markEnd();
		}

		String communityName = jsonObject.getString("communityName");
		if(Objects.nonEmpty(communityName)){
			query.like("h.communityName", communityName);
		}

		String buildNo = jsonObject.getString("buildNo");
		if(Objects.nonEmpty(buildNo)){
			query.eq("h.buildNo", buildNo);
		}

		String buildName = jsonObject.getString("buildName");
		if(Objects.nonEmpty(buildName)){
			query.like("h.buildName", buildName);
		}

		String unitName = jsonObject.getString("unitName");
		if(Objects.nonEmpty(unitName)){
			query.like("h.unitName", unitName);
		}

		String mergeName = jsonObject.getString("mergeName");
		if(Objects.nonEmpty(mergeName)){
			query.like("h.mergeName", mergeName);
		}

		String name = jsonObject.getString("name");
		if(Objects.nonEmpty(name)){
			query.like("h.name", name);
		}

		String communityNo = jsonObject.getString("communityNo");
		if(Objects.nonEmpty(communityNo)){
			query.eq("h.communityNo", communityNo);
		}

		String ownerName = jsonObject.getString("ownerName");
		if(Objects.nonEmpty(ownerName)){
			query.like("h.ownerName", ownerName);
		}

		String ownerMobile = jsonObject.getString("ownerMobile");
		if(Objects.nonEmpty(ownerMobile)){
			query.like("h.ownerMobile", ownerMobile);
		}

		//排除住户已经占用的房屋
		String residentNo = jsonObject.getString("residentNo");
		if(Objects.nonEmpty(residentNo)){
			List<String> notInHouseIds = getResidentHouseList(jsonObject);
			if(Objects.nonEmpty(notInHouseIds)){
				query.noIn("h.id", notInHouseIds);
			}
		}

		query.eq("h.deleted", false)
				.order("h.createTime", "desc");

		query.page(jsonObject);

		try{
			// 获取房屋的条件查询信息
			PermissionNameDto dto = getHousePermissionNameDto();

			// 根据条件查询 全部数据、我的数据、下属的数据
			setQueryDataSql(jsonObject, dto, query);

			// 根据以上条件查询房屋的分页数据
			Page<Map<String, Object>> page = houseDao.loadPage(query);

			// 根据管家ID 获取到员工的所有信息 填充到数据集里
			this.setEmployeeNames(page.getList());

			return page;
		}catch (Exception e){
		}

		return new Page<>();
	}

	private JSONResult checkHouse(House house, Boolean isUpadate){
		//房屋是否已经存在
		FindOne<House> find = jdbcSession.findOne(House.class)
				.eq("deleted", false)
				.eq("communityNo", house.getCommunityNo())
				.eq("buildName", house.getBuildName())
				.eq("floorName", house.getFloorName())
				.eq("name", house.getName());

		if(isUpadate){
			find.noEq("id", house.getId());
		}

		if (Objects.nonEmpty(house.getUnitName())) {
			find.eq("unitName", house.getUnitName());
		}
		House item = find.exe();

		if (Objects.nonNull(item)) {
			return Result.error(0, "当前楼栋下已有相同的房屋信息，请修改后重试");
		}

		return null;
	}

	private void setQueryDataSql(JSONObject jsonObject, PermissionNameDto dto, Condition query){
		Integer dataPvType = jsonObject.getIntValue("dataPvType");
		if(dataPvType == 2){
			DataPermissionQuery.setChildDepartmentDataSql(query,dto, null);
		}else if(dataPvType == 3){
			DataPermissionQuery.setManageDataSql(query,dto, null);
		}else{
			DataPermissionQuery.setAllDataSql(query, dto, null);
		}
	}

	public PermissionNameDto getBuildPermissionNameDto(){
		PermissionNameDto dto = new PermissionNameDto();
		// 指定人员
		dto.setManageUserId("employeeNos");
		// 创建人的部门
		dto.setDepartmentNo("createDepartment");
		// 创建人的部门树
		dto.setDepartmentNos("createChildDepartment");
		// 指定部门
		dto.setManageDepartmentNo("departmentNo");

		return dto;
	}

	public PermissionNameDto getCommunityPermissionNameDto(){

		PermissionNameDto dto = new PermissionNameDto();
		// 指定人员
		dto.setManageUserId("employee_ids");
		// 创建人的部门
		dto.setDepartmentNo("createDepartment");
		// 创建人的部门树
		dto.setDepartmentNos("createChildDepartment");
		// 指定部门
		dto.setManageDepartmentNo("parent_id");

		return dto;
	}

	public PermissionNameDto getHousePermissionNameDto(){
		PermissionNameDto dto = new PermissionNameDto();
		dto.setCreateUserId("h.createUserId");
		// 指定人员
		dto.setManageUserId("b.employeeNos");
		// 创建人的部门
		dto.setDepartmentNo("h.createDepartment");
		// 创建人的部门树
		dto.setDepartmentNos("h.createChildDepartment");
		// 指定部门
		dto.setManageDepartmentNo("h.departmentNo");

		return dto;
	}

	public PermissionNameDto getShopsPermissionNameDto(){

		PermissionNameDto dto = new PermissionNameDto();
		dto.setCreateUserId("s.createUserId");
		// 指定人员
		dto.setManageUserId("b.employeeNos");
		// 创建人的部门
		dto.setDepartmentNo("s.createDepartment");
		// 创建人的部门树
		dto.setDepartmentNos("s.createChildDepartment");
		// 指定部门
		dto.setManageDepartmentNo("s.departmentNo");

		return dto;
	}

	public PermissionNameDto getParkingsPermissionNameDto(){

		PermissionNameDto dto = new PermissionNameDto();
		dto.setCreateUserId("p.createUserId");
		// 指定人员
		dto.setManageUserId("b.employeeNos");
		// 创建人的部门
		dto.setDepartmentNo("p.createDepartment");
		// 创建人的部门树
		dto.setDepartmentNos("p.createChildDepartment");
		// 指定部门
		dto.setManageDepartmentNo("p.departmentNo");

		return dto;
	}

	private void setEmployeeNames(List<Map<String, Object>> list) throws Exception {
		Set<String> employeeIds = new HashSet<>();
		list.forEach(item -> {
			String strs = String.valueOf(item.get("employeeNos"));
			if(Objects.nonEmpty(strs)){
				employeeIds.addAll(Arrays.asList(strs.split(",")));
			}
		});

		Map<String, JSONObject> employeeMap = getEmployeeMap(new ArrayList<>(employeeIds));
		list.forEach(item -> {
			String employeeNos = String.valueOf(item.get("employeeNos"));
			if(Objects.nonEmpty(employeeNos)){
				List<String> ids = Arrays.asList(employeeNos.split(","));
				item.put("employeeNames", getEmployeeNames(ids, employeeMap));
			}
		});
	}

	private List<String> getEmployeeNames(List<String> ids, Map<String, JSONObject> employeeMap){
		List<String> employeeNames = new ArrayList<>();
		ids.forEach(id ->{
			JSONObject employee = employeeMap.get(id);
			if (Objects.nonNull(employee)) {
				employeeNames.add(employee.getString("name"));
			}
		});
		return employeeNames;
	}

	private List<String> getResidentHouseList(JSONObject jsonObject) throws Exception{
		List<String> notInHouseIds = new ArrayList<>();
		JSONObject residentHouseResult = doveClient.post("/crm/resident/service/getResidentHouseList", (http) -> {
			JSONObject body = new JSONObject();
			body.put("residentNo",jsonObject.getString("jsonObject"));
			body.put("bindType", jsonObject.getJSONArray("bindType"));
			body.put("type",jsonObject.getString("type"));
			body.put("pageSize",Short.MAX_VALUE);
			http.setBody(body.toJSONString());
		});
		if (residentHouseResult.getIntValue("code") == 200) {
			JSONArray residentHouseList = residentHouseResult.getJSONObject("data").getJSONArray("list");
			if (Objects.nonEmpty(residentHouseList)) {
				for (int i = 0; i < residentHouseList.size(); i++) {
					JSONObject residentHouse = residentHouseList.getJSONObject(i);
					notInHouseIds.add(residentHouse.getString("houseNo"));
				}
			}
		}

		return notInHouseIds;
	}

	private Map<String, JSONObject> getEmployeeMap(List<String> employeeIds) throws Exception {
		JSONObject result = doveClient.post("/basic/employee/service/list", http -> {
			JSONObject body = new JSONObject();
			body.put("id", employeeIds);
			body.put("pageNo", 1);
			body.put("pageSize", 1000000);
			http.setBody(body.toString());
		});

		JSONArray data = result.getJSONObject("data").getJSONArray("list");

		if (Objects.isNull(data)) {
			return null;
		}

		Map<String, JSONObject> employeeMap = new HashMap<>();
		data.forEach(item -> {
			JSONObject obj = (JSONObject) item;
			employeeMap.put(obj.getString("id"), obj);
		});

		return employeeMap;
	}

	private void setProperty(House item, Map<String,Object> map) throws Exception {
		Object salesStatus = map.get("salesStatus");
		if (Objects.nonNull(salesStatus)) {
			item.setSalesStatus(EnumUtil.getEnum(SalesStatus.class, String.valueOf(salesStatus)));
		}

		Object harvestStatus = map.get("harvestStatus");
		if (Objects.nonNull(harvestStatus)) {
			item.setHarvestStatus(EnumUtil.getEnum(HarvestStatus.class, String.valueOf(harvestStatus)));
		}

		Object decoratedStasus = map.get("decoratedStasus");
		if (Objects.nonNull(decoratedStasus)) {
			item.setDecoratedStasus(EnumUtil.getEnum(DecoratedStasus.class, String.valueOf(decoratedStasus)));
		}

		Object resideStasus = map.get("resideStasus");
		if (Objects.nonNull(resideStasus)) {
			item.setResideStasus(EnumUtil.getEnum(ResideStasus.class, String.valueOf(resideStasus)));
		}

		Object rentStasus = map.get("rentStasus");
		if (Objects.nonNull(rentStasus)) {
			item.setRentStasus(EnumUtil.getEnum(RentStasus.class, String.valueOf(rentStasus)));
		}

		Object propertyType = map.get("propertyType");
		if (Objects.nonNull(propertyType)) {
			item.setPropertyType(EnumUtil.getEnum(PropertyType.class, String.valueOf(propertyType)));
		}
	}

	private void setProperty(House item, JSONObject jsonObject) throws Exception {
		item.setMergeName(item.getFloorName() + item.getName());

		String salesStatus = jsonObject.getString("salesStatus");
		if (Objects.nonNull(salesStatus)) {
			item.setSalesStatus(EnumUtil.getEnum(SalesStatus.class, salesStatus));
		}

		String harvestStatus = jsonObject.getString("harvestStatus");
		if (Objects.nonNull(harvestStatus)) {
			item.setHarvestStatus(EnumUtil.getEnum(HarvestStatus.class, harvestStatus));
		}

		String decoratedStasus = jsonObject.getString("decoratedStasus");
		if (Objects.nonNull(decoratedStasus)) {
			item.setDecoratedStasus(EnumUtil.getEnum(DecoratedStasus.class, decoratedStasus));
		}

		String resideStasus = jsonObject.getString("resideStasus");
		if (Objects.nonNull(resideStasus)) {
			item.setResideStasus(EnumUtil.getEnum(ResideStasus.class, resideStasus));
		}

		String rentStasus = jsonObject.getString("rentStasus");
		if (Objects.nonNull(rentStasus)) {
			item.setRentStasus(EnumUtil.getEnum(RentStasus.class, rentStasus));
		}

		String propertyType = jsonObject.getString("propertyType");
		if (Objects.nonNull(propertyType)) {
			item.setPropertyType(EnumUtil.getEnum(PropertyType.class, propertyType));
		}

		Community com = Pool.get(Community.class, item.getCommunityNo());
		item.setDepartmentNo(com.getParent_id());
		item.setDepartmentNos(com.getParent_ids());

		// 更新小区标签信息
		List<String> tags = this.saveOrUpdateTag(item);
		if(Objects.nonEmpty(tags)){
			item.setTags(tags);
		}
	}

	private List<String> getChildDepartments(List<String> departments) throws Exception {
		JSONObject result = doveClient.post("/basic/department/service/getChildDepartments", http->{
			JSONObject body = new JSONObject();
			body.put("pidList", departments);
			http.setBody(body.toString());
		});

		if (result.getIntValue("code") == 200) {
			JSONArray data = result.getJSONArray("data");
			if(Objects.nonEmpty(data)){
				return JSONObject.parseArray(data.toJSONString(), String.class);
			}
		}
		return new ArrayList<>();
	}

	private List<String> getResidentHouseIds(JSONObject jsonObject) throws Exception {
		List<String> list = new ArrayList<>();
		JSONObject residentHouseResult = doveClient.post("/crm/resident/service/getResidentHouseList", (http) -> {
			JSONObject body = new JSONObject();
			body.put("buildNo", jsonObject.getString("buildNo"));
			body.put("bindType", jsonObject.getJSONArray("bindType"));
			body.put("type", "1");
			body.put("pageSize",Short.MAX_VALUE);
			http.setBody(body.toJSONString());
		});
		if (residentHouseResult.getIntValue("code") == 200) {
			JSONArray residentHouseList = residentHouseResult.getJSONObject("data").getJSONArray("list");
			if (Objects.nonEmpty(residentHouseList)) {
				for (int i = 0; i < residentHouseList.size(); i++) {
					JSONObject residentHouse = residentHouseList.getJSONObject(i);
					list.add(residentHouse.getString("houseNo"));
				}
			}
		}
		return list;
	}

	private JSONObject communitie2JSONObject(Community community) {
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("id", community.getId());
		jsonObject.put("pid", community.getParent_ids());
		jsonObject.put("label", community.getName());
		jsonObject.put("level", 2);
		return jsonObject;
	}

	private JSONObject root2JSONObject(Token token) throws Exception{
		JSONObject resultDepartment = doveClient.post("/basic/department/service/get", (http) -> {
			http.addHeader("companyNo", token.getCompanyNo());
			http.addParam("id", Root.ROOT_ID);
		});
		JSONObject department = resultDepartment.getJSONObject("data");
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("id", department.getString("id"));
		jsonObject.put("label",department.getString("name"));
		jsonObject.put("level", 1);
		return jsonObject;
	}

	private JSONObject build2JSONObject(String communityId, Building build) {
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("id", build.getId());
		jsonObject.put("pid", communityId);
		jsonObject.put("label", Objects.nonEmpty(build.getUnitName()) ? build.getName() + build.getUnitName() : build.getName());
		jsonObject.put("level", 3);
		return jsonObject;
	}

	private JSONObject house2JSONObject(Building build, Map<String,Object> house) {
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("id", house.get("id"));
		jsonObject.put("pid", build.getId());
		StringBuffer buf = new StringBuffer();
		if (Objects.nonEmpty(build.getUnitName())) {
			buf.append(build.getCommunityName()).append(">");
			buf.append(build.getName()).append(build.getUnitName()).append(">");
			buf.append(house.get("floorName")).append(house.get("name"));
		} else {
			buf.append(build.getCommunityName()).append(">").append(build.getName()).append(">");
			buf.append(house.get("floorName")).append(house.get("name"));
		}
		jsonObject.put("build", buf.toString());
		jsonObject.put("label", house.get("floorName").toString() + house.get("name").toString());
		jsonObject.put("user", true);
		jsonObject.put("level", 4);
		return jsonObject;
	}

	private void updateChargeSetting(House house) throws Exception {
		doveClient.post("/payfees/charge/choose/updateChargeSetting", http -> {
			http.setBody(house.toJSON().toJSONString());
		});
	}

	private XSSFWorkbook generateWorkBook() throws Exception {
		// 生成模板文件
		XSSFWorkbook wb = new XSSFWorkbook();

		// 设置头信息
		Sheet sheet = setSheetCellStyle(wb);

		//设置下拉框数据
		setDataValidation(sheet, 3);

		int cellNum = 0;

		Row row = sheet.createRow(3);
		row.createCell(cellNum++).setCellValue("七星商业广场");
		row.createCell(cellNum++).setCellValue("1栋");
		row.createCell(cellNum++).setCellValue("1单元");

		DataFormat format = wb.createDataFormat();
		CellStyle style= wb.createCellStyle();
		style.setDataFormat(format.getFormat("@"));

		int nameNum = cellNum++;
		Cell cellName = row.createCell(nameNum);
		cellName.setCellStyle(style);
		cellName.setCellValue("1");

		nameNum = cellNum++;
		Cell cellFloorName = row.createCell(nameNum);
		cellFloorName.setCellStyle(style);
		cellFloorName.setCellValue("01");

		row.createCell(cellNum++).setCellValue("01A");
		row.createCell(cellNum++).setCellValue("普通住宅");
		row.createCell(cellNum++).setCellValue("2室2厅2卫");
		row.createCell(cellNum++).setCellValue("98");
		row.createCell(cellNum++).setCellValue("90");
		row.createCell(cellNum++).setCellValue("90");
		row.createCell(cellNum++).setCellValue("未售");
		row.createCell(cellNum++).setCellValue("未收房");
		row.createCell(cellNum++).setCellValue("未装修");
		row.createCell(cellNum++).setCellValue("未入住");
		row.createCell(cellNum++).setCellValue("未出租");
		row.createCell(cellNum++).setCellValue("张小明");

		nameNum = cellNum++;
		Cell cellMobile = row.createCell(nameNum);
		cellMobile.setCellStyle(style);
		cellMobile.setCellValue("18888888888");

		nameNum = cellNum++;
		Cell cellCardNo = row.createCell(nameNum);
		cellCardNo.setCellStyle(style);
		cellCardNo.setCellValue("430535198012080000");

		// 自动列宽
		for (int i = 0; i < cellNum; i++) {
			ExcelUtil.autoSizeColumn(sheet, i);
		}
		return wb;
	}

	private Sheet setSheetCellStyle(XSSFWorkbook wb) {
		//设置字体颜色+文字水平居中
		CellStyle fontColorCellStyle = wb.createCellStyle();
		XSSFFont font = wb.createFont();
		XSSFColor color = new XSSFColor(new java.awt.Color(255, 0, 0));
		font.setColor(color);
		fontColorCellStyle.setAlignment(HorizontalAlignment.CENTER);
		fontColorCellStyle.setFont(font);

		// 允许文字换行
		XSSFFont fontStyle = wb.createFont(); // 字体样式
//		fontStyle.setBold(true);// 加粗
		fontStyle.setFontName("宋体");
		fontStyle.setFontHeightInPoints((short) 16); // 大小
		CellStyle noticeCellStyle = wb.createCellStyle();
		noticeCellStyle.setWrapText(true);
		noticeCellStyle.setFont(fontStyle);

		// 文字水平居中
		CellStyle fontCellStyle = wb.createCellStyle();
		fontCellStyle.setAlignment(HorizontalAlignment.CENTER);


		Sheet sheet = wb.createSheet();


		// 设置第1行 合并16格
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 18));
		// 设置第2行 合并格
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 10));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 11, 15));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 16, 18));

		//第1行 须知内容
		Row noticeRow = sheet.createRow(0);
		Cell noticeRowCell = noticeRow.createCell(0);
		noticeRowCell.setCellStyle(noticeCellStyle);
		StringBuffer notice = new StringBuffer();
		notice.append("填写须知：").append("\n")
				.append(" <1>不能在本excel表中对项目、楼栋/单元信息、进行增加、删除、修改，若项目、楼栋/单元不足，请进入“企业管理后台-应用管理-资产管理”进行添加，完成后重新下载新模板；").append("\n")
				.append(" <2>红色字段为必填字段，黑色字段为选填字段；").append("\n")
				.append(" <3>房屋信息为必填项，业主信息可不填，业主信息一旦填写一项必填项需填满才能导入；").append("\n")
				.append(" <4>项目/小区：请先到资产管理-项目列表先添加好或先通过excel导入好，再到本excel里填入；").append("\n")
				.append(" <5>楼栋/单元：请先到资产管理-楼栋/单元列表先添加好或先通过excel导入好，再到本excel里填入；").append("\n");

		noticeRowCell.setCellValue(notice.toString());
		//自动设置行高
		int colLength = noticeRowCell.getStringCellValue().getBytes().length * 5;
		noticeRow.setHeight((short) (colLength));

		//第2行 员工信息：
		Row firstRow = sheet.createRow(1);
		Cell firstRowCell = firstRow.createCell(0);
		firstRowCell.setCellValue("房屋信息");
		firstRowCell.setCellStyle(fontCellStyle);
		firstRowCell = firstRow.createCell(11);
		firstRowCell.setCellValue("房屋状态");
		firstRowCell.setCellStyle(fontCellStyle);
		firstRowCell = firstRow.createCell(16);
		firstRowCell.setCellValue("业主信息");
		firstRowCell.setCellStyle(fontCellStyle);

		//第3行 表头
		Row headerRow = sheet.createRow(2);
		for (int i = 0; i < EXCEL_IMPORT_HEADER.length; i++) {
			Cell cell = headerRow.createCell(i);
			if (i == 0 || i == 1 || i == 3 || i == 4 || i == 10 || i == 16 || i == 17) {
				cell.setCellStyle(fontColorCellStyle);
			} else {
				cell.setCellStyle(fontCellStyle);
			}
			cell.setCellValue(EXCEL_IMPORT_HEADER[i]);
		}

		return sheet;
	}

	public XSSFWorkbook generateWorkBook(List<Map<String,Object>> list) throws Exception {
		House house = Pool.newInstance(House.class);
		// 生成模板文件
		XSSFWorkbook wb = new XSSFWorkbook();

		// 设置头信息
		Sheet sheet = setSheetCellStyle(wb);

		Set<String> ownerNos = new HashSet<>();
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> houseMap = list.get(i);
			Beans.from(houseMap).to(house).copy();
			setProperty(house,houseMap);
			if (Objects.nonEmpty(house.getOwnerNo())) {
				ownerNos.add(house.getOwnerNo());
			}
		}

		Map<String, JSONObject> residentMap = getResidentMap(new ArrayList<>(ownerNos));

		//设置下拉框数据
		setDataValidation(sheet, list.size() + 2);

		int cellNum = 0;
		for (int i = 0; i < list.size(); i++) {
			cellNum = 0;
			Map<String, Object> houseMap = list.get(i);
			Beans.from(houseMap).to(house).copy();
			setProperty(house,houseMap);
			Row row = sheet.createRow(i + 3);
			row.createCell(cellNum++).setCellValue(house.getCommunityName());
			row.createCell(cellNum++).setCellValue(house.getBuildName());
			row.createCell(cellNum++).setCellValue(house.getUnitName());

			DataFormat format = wb.createDataFormat();
			CellStyle style= wb.createCellStyle();
			style.setDataFormat(format.getFormat("@"));

			int nameNum = cellNum++;
			Cell cellName = row.createCell(nameNum);
			cellName.setCellStyle(style);
			cellName.setCellValue(house.getFloorName());

			nameNum = cellNum++;
			Cell cellFloorName = row.createCell(nameNum);
			cellFloorName.setCellStyle(style);
			cellFloorName.setCellValue(house.getName());

			row.createCell(cellNum++).setCellValue(house.getViewName());
			row.createCell(cellNum++).setCellValue(Objects.nonNull(house.getPropertyType()) ? house.getPropertyType().getDesc() : "");
			row.createCell(cellNum++).setCellValue(house.getFamily());

			if(Objects.nonNull(house.getBuildArea())){
				row.createCell(cellNum++).setCellValue(house.getBuildArea().setScale(2) + "");
			}else{
				row.createCell(cellNum++).setCellValue("");
			}

			if(Objects.nonNull(house.getUseArea())){
				row.createCell(cellNum++).setCellValue(house.getUseArea().setScale(2) + "");
			}else{
				row.createCell(cellNum++).setCellValue("");
			}

			row.createCell(cellNum++).setCellValue(house.getPayArea().setScale(2) + "");

			row.createCell(cellNum++).setCellValue(Objects.nonNull(house.getSalesStatus()) ? house.getSalesStatus().getDesc() : "");
			row.createCell(cellNum++).setCellValue(Objects.nonNull(house.getHarvestStatus()) ? house.getHarvestStatus().getDesc() : "");
			row.createCell(cellNum++).setCellValue(Objects.nonNull(house.getDecoratedStasus()) ? house.getDecoratedStasus().getDesc() : "");
			row.createCell(cellNum++).setCellValue(Objects.nonNull(house.getResideStasus()) ? house.getResideStasus().getDesc() : "");
			row.createCell(cellNum++).setCellValue(Objects.nonNull(house.getRentStasus()) ? house.getRentStasus().getDesc() : "");

			JSONObject resident = residentMap.get(house.getOwnerNo());
			if (Objects.nonEmpty(resident)) {
				row.createCell(cellNum++).setCellValue(resident.getString("realname"));

				nameNum = cellNum++;
				Cell cellMobile = row.createCell(nameNum);
				cellMobile.setCellStyle(style);
				cellMobile.setCellValue(resident.getString("mobile"));

				nameNum = cellNum++;
				Cell cellCardNo = row.createCell(nameNum);
				cellCardNo.setCellStyle(style);
				cellCardNo.setCellValue(resident.getString("cardNo"));

			}
		}

		// 自动列宽
		for (int i = 0; i < cellNum; i++) {
			ExcelUtil.autoSizeColumn(sheet, i);
		}
		return wb;
	}

	private void setDataValidation (Sheet sheet, Integer length) {

		CellRangeAddressList addressList = new CellRangeAddressList(3, length, 6, 6);

		DataValidationHelper helper = sheet.getDataValidationHelper();

		PropertyType[] types = PropertyType.values();
		String[] strs = new String[types.length];
		for (int i = 0; i < types.length; i++) {
			strs[i] = types[i].getDesc();
		}
		DataValidationConstraint constraint = helper.createExplicitListConstraint(strs);
		DataValidation dataValidation = helper.createValidation(constraint, addressList);

		if(dataValidation instanceof XSSFDataValidation) {
			dataValidation.setSuppressDropDownArrow(true);
			dataValidation.setShowErrorBox(true);
		}else {
			dataValidation.setSuppressDropDownArrow(false);
		}

		sheet.addValidationData(dataValidation);
	}

	public Map<String, JSONObject> getResidentMap(List<String> ids) throws Exception {
		if (Objects.nonEmpty(ids)) {
			JSONObject result = doveClient.post("/crm/resident/service/getResidentList", http -> {
				JSONObject body = new JSONObject();
				body.put("ids", ids);

				http.setBody(body.toString());
			});

			JSONArray data = result.getJSONObject("data").getJSONArray("list");

			if (Objects.isNull(result) || Objects.isNull(data)) {
				return new HashMap<>();
			}

			Map<String, JSONObject> residentMap = new HashMap<>();
			data.forEach(item -> {
				JSONObject obj = (JSONObject) item;
				residentMap.put(((JSONObject) item).getString("id"), obj);
			});

			return residentMap;
		} else {
			return new HashMap<>();
		}
	}

	private void checkExcelData(Row row, JSONObject json) {

		String communityName = ExcelUtil.getCellValue(row.getCell(0));
		Community community = null;
		if (Objects.nonEmpty(communityName)) {
			community = jdbcSession.findOne(Community.class)
					.eq("deleted", false)
					.eq("name", communityName)
					.exe();
			if (Objects.nonNull(community)) {
				json.put("communityNo", community.getId());
				json.put("communityName", community.getName());
			}
		}

		String buildName = ExcelUtil.getCellValue(row.getCell(1));
		if (Objects.nonEmpty(buildName)) {
			if (Objects.nonNull(community)) {

				FindOne<Building> buildFind = jdbcSession.findOne(Building.class)
						.eq("deleted", false)
						.eq("communityNo", community.getId())
						.eq("name", buildName);

				String unitName = ExcelUtil.getCellValue(row.getCell(2));
				if (Objects.nonEmpty(unitName)) {
					buildFind.eq("unitName", unitName);
				}

				Building building = buildFind.exe();
				if (Objects.nonNull(building)) {
					json.put("buildName", buildName);
					json.put("unitName", unitName);
					json.put("buildNo", building.getId());
				}
			}
		}

		String floorName = ExcelUtil.getCellValue(row.getCell(3));
		json.put("floorName", floorName);

		String name = ExcelUtil.getCellValue(row.getCell(4));
		json.put("name", name);

		String viewName = ExcelUtil.getCellValue(row.getCell(5));
		json.put("viewName", viewName);

		String propertyType = ExcelUtil.getCellValue(row.getCell(6));
		json.put("propertyType", propertyType);

		String family = ExcelUtil.getCellValue(row.getCell(7));
		json.put("family", family);

		BigDecimal buildArea = ExcelUtil.getBigDecimalFromCell(row, 8);
		json.put("buildArea", buildArea);

		BigDecimal useArea = ExcelUtil.getBigDecimalFromCell(row, 9);
		json.put("useArea", useArea);

		BigDecimal payArea = ExcelUtil.getBigDecimalFromCell(row, 10);
		json.put("payArea", payArea);

		String salesStatus = ExcelUtil.getCellValue(row.getCell(11));
		json.put("salesStatus", salesStatus);

		String harvestStatus = ExcelUtil.getCellValue(row.getCell(12));
		json.put("harvestStatus", harvestStatus);

		String decoratedStasus = ExcelUtil.getCellValue(row.getCell(13));
		json.put("decoratedStasus", decoratedStasus);

		String resideStasus = ExcelUtil.getCellValue(row.getCell(14));
		json.put("resideStasus", resideStasus);

		String rentStasus = ExcelUtil.getCellValue(row.getCell(15));
		json.put("rentStasus", rentStasus);

		String realname = ExcelUtil.getCellValue(row.getCell(16));
		json.put("realname", realname);

		String mobile = ExcelUtil.getCellValue(row.getCell(17));
		json.put("mobile", mobile);

		String cardNo = ExcelUtil.getCellValue(row.getCell(18));
		json.put("cardNo", cardNo);
	}

	private void checkExcelData(Row row, int index, List<String> errMsgs, Map<Integer,Integer> errCount) {

		String communityName = ExcelUtil.getCellValue(row.getCell(0));
		Community community = null;
		if (Objects.isEmpty(communityName)) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：【项目/小区】不能为空");
		} else {
			community = jdbcSession.findOne(Community.class)
					.eq("deleted", false)
					.eq("name", communityName)
					.exe();
			if (Objects.isNull(community)) {
				errCount.put(index, 1);
				errMsgs.add("第" + index + "行,未获取到当前【小区信息】,请检查");
			}
		}

		String buildName = ExcelUtil.getCellValue(row.getCell(1));
		if (Objects.isEmpty(buildName)) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：【楼栋】不能为空");
		} else {
			if (Objects.nonNull(community)) {
				FindOne<Building> buildFind = jdbcSession.findOne(Building.class)
						.eq("deleted", false)
						.eq("communityNo", community.getId())
						.eq("name", buildName);

				String unitName = ExcelUtil.getCellValue(row.getCell(2));
				if (Objects.nonEmpty(unitName)) {
					buildFind.eq("unitName", unitName);
				}

				Building building = buildFind.exe();
				if (Objects.isNull(building)) {
					errCount.put(index, 1);
					errMsgs.add("第" + index + "行,当前小区下未获取到【楼栋信息】,请检查");
				}
			}
		}

		try {
			String floorName = ExcelUtil.getCellValue(row.getCell(3));
			if (Objects.isEmpty(floorName)) {
				errCount.put(index, 1);
				errMsgs.add("第" + index + "行：【楼层】不能为空");
			}else{
				new Integer(floorName);
			}
		}catch (Exception e){
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：【楼层】录入格式有误");
		}

		String houseName = ExcelUtil.getCellValue(row.getCell(4));
		if (Objects.isEmpty(houseName)) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：【房屋】不能为空");
		}

		String propertyType = ExcelUtil.getCellValue(row.getCell(6));
		if (Objects.nonEmpty(propertyType) && Objects.isNull(PropertyType.get(propertyType))) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：输入的【物业类型】不匹配");
		}

		try {
			ExcelUtil.getBigDecimalFromCell(row, 8);
		} catch (Exception e) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：【建筑面积】校验出现异常请检查");
		}

		try {
			ExcelUtil.getBigDecimalFromCell(row, 9);
		} catch (Exception e) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：【实用面积】校验出现异常请检查");
		}

		try {
			BigDecimal payArea = ExcelUtil.getBigDecimalFromCell(row, 10);
			if(Objects.isNull(payArea)){
				errCount.put(index, 1);
				errMsgs.add("第" + index + "行：【计费面积】不能为空");
			}
		} catch (Exception e) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：【计费面积】校验出现异常请检查");
		}


		String salesStatus = ExcelUtil.getCellValue(row.getCell(11));
		if (Objects.nonEmpty(salesStatus) && Objects.isNull(SalesStatus.get(salesStatus))) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：输入的【销售状态】不匹配");
		}

		String harvestStatus = ExcelUtil.getCellValue(row.getCell(12));
		if (Objects.nonEmpty(harvestStatus) && Objects.isNull(HarvestStatus.get(harvestStatus))) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：输入的【收房状态】不匹配");
		}

		String decoratedStasus = ExcelUtil.getCellValue(row.getCell(13));
		if (Objects.nonEmpty(decoratedStasus) && Objects.isNull(DecoratedStasus.get(decoratedStasus))) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：输入的【装修状态】不匹配");
		}

		String resideStasus = ExcelUtil.getCellValue(row.getCell(14));
		if (Objects.nonEmpty(resideStasus) && Objects.isNull(ResideStasus.get(resideStasus))) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：输入的【入住状态】不匹配");
		}

		String rentStasus = ExcelUtil.getCellValue(row.getCell(15));
		if (Objects.nonEmpty(rentStasus) && Objects.isNull(RentStasus.get(rentStasus))) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：输入的【出租状态】不匹配");
		}

		String realname = ExcelUtil.getCellValue(row.getCell(16));
		String mobile = ExcelUtil.getCellValue(row.getCell(17));

		if (isMixture(realname, mobile)) {
			errCount.put(index, 1);
			errMsgs.add("第" + index + "行：【姓名和手机号码一旦填写一项必填项需填满才能导入】");
		}
	}

	private void setExcelData(House house, JSONObject json) throws Exception {
		house.setCommunityNo(json.getString("communityNo"));
		house.setCommunityName(json.getString("communityName"));
		house.setBuildNo(json.getString("buildNo"));
		house.setBuildName(json.getString("buildName"));
		house.setUnitName(json.getString("unitName"));
		house.setFloorName(json.getString("floorName"));
		house.setName(json.getString("name"));
		house.setPropertyType(PropertyType.get(json.getString("propertyType")));
		house.setFamily(json.getString("family"));
		house.setBuildArea(json.getBigDecimal("buildArea"));
		house.setUseArea(json.getBigDecimal("useArea"));
		house.setPayArea(json.getBigDecimal("payArea"));
		house.setViewName(json.getString("viewName"));
		house.setSalesStatus(SalesStatus.get(json.getString("salesStatus")));
		house.setHarvestStatus(HarvestStatus.get(json.getString("harvestStatus")));
		house.setDecoratedStasus(DecoratedStasus.get(json.getString("decoratedStasus")));
		house.setResideStasus(ResideStasus.get(json.getString("resideStasus")));
		house.setRentStasus(RentStasus.get(json.getString("rentStasus")));
		house.setMergeName(house.getFloorName() + house.getName());

		Community com = Pool.get(Community.class, house.getCommunityNo());
		house.setDepartmentNo(com.getParent_id());
		house.setDepartmentNos(com.getParent_ids());

		// 更新小区标签信息
		List<String> tags = this.saveOrUpdateTag(house);
		if(Objects.nonEmpty(tags)){
			house.setTags(tags);
		}
	}


	/**
	 * 全部不为空
	 *
	 * @param strs
	 * @return
	 */
	public static boolean isAllNotEmpty(String... strs) {
		if (Objects.isEmpty(strs)) {
			return false;
		}
		for (String str : strs) {
			if (Objects.isEmpty(str)) {
				return false;
			}
		}
		return true;
	}

	/**
	 * 全部为空
	 *
	 * @param strs
	 * @return
	 */
	public static boolean isAllEmpty(String... strs) {
		if (Objects.isEmpty(strs)) {
			return true;
		}
		for (String str : strs) {
			if (Objects.nonEmpty(str)) {
				return false;
			}
		}
		return true;
	}

	/**
	 * 不全为空，也不全为非空
	 *
	 * @param strs
	 * @return
	 */
	public static boolean isMixture(String... strs) {
		return !isAllEmpty(strs) && !isAllNotEmpty(strs);
	}


	/**
	 * 忽略数据库 楼和楼关键字批量查找房屋信息
	 * 避免因为存储不规范造成查找匹配不到数据的问题
	 * @param jsonObject
	 * @return
	 */
	@ReqMapping("/getHouseIdListByMultipleQuery")
	public Set<String> getHouseIdListByMultipleQuery(JSONObject jsonObject) {
		JSONArray communitySet = jsonObject.getJSONArray("communitySet");
		JSONArray communityNoSet = jsonObject.getJSONArray("communityNoSet");
		JSONArray buildSet = jsonObject.getJSONArray("buildSet");
		JSONArray houseNumberSet = jsonObject.getJSONArray("houseNumberSet");
		JSONArray houseNoSet = jsonObject.getJSONArray("houseNoSet");
		JSONArray unitNameSet = jsonObject.getJSONArray("unitNameSet");
		JSONArray chargeNameSet = jsonObject.getJSONArray("chargeNameSet");


		QueryCondition find = Condition.createQuery(false);
		find.eq("deleted",false);
		if(!Objects.isEmpty(communitySet)){
			find.in("communityName",communitySet);
		}
		if(!Objects.isEmpty(communityNoSet)){
			find.in("communityNo",communityNoSet);
		}
		if(!Objects.isEmpty(houseNoSet)){
			find.in("id",houseNoSet);
		}
		if(!Objects.isEmpty(buildSet)){
			buildSet = ConvertUtils.replaceString2JSONArray(buildSet,"栋", "");
			find.in("REPLACE(`buildName`,'栋','')",buildSet);
		}
		if(!Objects.isEmpty(houseNumberSet)){
			find.in("mergeName",houseNumberSet);
		}
		if(!Objects.isEmpty(unitNameSet)) {
			unitNameSet =ConvertUtils.replaceString2JSONArray(unitNameSet,"单元", "");
			find.in("REPLACE(`unitName`,'单元','')", unitNameSet);
		}
		if(!Objects.isEmpty(chargeNameSet)){
			find.in("ownerName", chargeNameSet);
		}
		List<House> houseList = houseDao.getHouseIdListByMultipleQuery(find);

		Set<String> collect = houseList.stream().map(h -> h.getId()).collect(Collectors.toSet());
		return collect;
	}

	@ReqMapping("/batch/ids")
	public Object batchGetHouseByIds(JSONObject jsonObject) {
		JSONArray ids = jsonObject.getJSONArray("ids");
		if(Objects.isEmpty(ids)){
			return new ArrayList<>();
		}
		List<House> houseList = jdbcSession.findArray(House.class)
				.in("id", ids)
				.exe();
		return houseList;
	}

}
